package com.weixin.base.dao.impl;

import java.util.Calendar;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;

import cn.org.rapid_framework.page.Page;
import cn.org.rapid_framework.page.PageRequest;

import com.weixin.base.dao.AreaManagerDao;
import com.weixin.base.util.Tools;
import com.weixin.common.BaseSpringJdbcDao;
import com.weixin.entity.TbArea;
import com.weixin.entity.TbCenterNorelArea;
import com.weixin.entity.TbMobileRelArea;

public class AreaManagerDaoImpl extends BaseSpringJdbcDao<TbArea, Integer> implements AreaManagerDao {
	public Class getEntityClass() {
		return TbArea.class;
	}
	
	@Override
	public boolean addAreaCenter(TbCenterNorelArea entity) {
		boolean result=false;
		
		String sql = "insert into tbcenternorelarea " 
			 + " (sCenterNo,nProvinceId,sProvinceName,sProvinceCode,nCityId,sCityName,sCityCode,nOP) " 
			 + " values "
			 + " (:sCenterNo,:nProvinceId,:sProvinceName,:sProvinceCode,:nCityId,:sCityName,:sCityCode,:nOP) ";
		try {
			int i=getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(entity));
			if(i>0){
				result=true;
			}
		} catch (Exception e) {
			result=false;
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public boolean addAreaMoblie(TbMobileRelArea entity) {
		boolean result=false;
		
		String sql = "insert into tbmobilerelarea " 
			 + " (sMoblieNo,nProvinceId,sProvinceName,sProvinceCode,nCityId,sCityName,sCityCode,nOP) " 
			 + " values "
			 + " (:sMoblieNo,:nProvinceId,:sProvinceName,:sProvinceCode,:nCityId,:sCityName,:sCityCode,:nOP) ";
		try {
			int i=getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(entity));
			if(i>0){
				result=true;
			}
		} catch (Exception e) {
			result=false;
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public boolean deleteAreaCenter(int ncenterNoRelAreaId) {
		boolean result=false;
		
		String sSQL="DELETE FROM tbcenternorelarea WHERE nCenterNoRelAreaId="+ncenterNoRelAreaId;
		try {
			int i=getSimpleJdbcTemplate().update(sSQL);
			if(i>0){
				result=true;
			}
		} catch (Exception e) {
			result=false;
			e.printStackTrace();
		}
		
		return result;
	}

	@Override
	public boolean deleteAreaMoblie(int nmobileRelAreaId) {
		boolean result=false;
		
		String sSQL="DELETE FROM tbmobilerelarea WHERE nMobileRelAreaId="+nmobileRelAreaId;
		try {
			int i=getSimpleJdbcTemplate().update(sSQL);
			if(i>0){
				result=true;
			}
		} catch (Exception e) {
			result=false;
			e.printStackTrace();
		}
		
		return result;
	}
	
	@Override
	public boolean updateAreaCenter(TbCenterNorelArea entity) {
		boolean result=false;
		String sql = "update tbcenternorelarea SET "
			+ " sCenterNo = :sCenterNo,nOP = :nOP  "
			+ " where nCenterNoRelAreaId=:nCenterNoRelAreaId";
		
		try {
			int i=getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(entity));
			if(i>0){
				result=true;
			}
		} catch (Exception e) {
			result=false;
			e.printStackTrace();
		}
		
		return result;
	}

	@Override
	public boolean updateAreaMoblie(TbMobileRelArea entity) {
		boolean result=false;
		String sql = "update tbmobilerelarea SET "
			+ " sMoblieNo = :sMoblieNo,nOP = :nOP  "
			+ " where nMobileRelAreaId=:nMobileRelAreaId";
		
		try {
			int i=getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(entity));
			if(i>0){
				result=true;
			}
		} catch (Exception e) {
			result=false;
			e.printStackTrace();
		}
		
		return result;
	}

	@Override
	public Page searchAreaMoblieByPageReq(PageRequest<Map> pageRequest) {
		Map map = pageRequest.getFilters();
		String sql = "";
		String order = "  order by nMobileRelAreaId desc";
		String sName = "";
		int nprovinceId = 0;
		int nOP = 0;
		int ncityId=0;
		
		if(map.get("sName") != null)
			sName = (String) map.get("sName");
		if(map.get("nprovinceId") != null&&!map.get("nprovinceId").equals(""))
			nprovinceId = Integer.parseInt(map.get("nprovinceId").toString());
		if(map.get("ncityId") != null)
			ncityId = Integer.parseInt(map.get("ncityId").equals("")?"0":map.get("ncityId").toString());
		if(map.get("nop") != null)
			nOP = Integer.parseInt(map.get("nop").toString());
		
		if (!sName.isEmpty()) {
			sql += " AND sMoblieNo LIKE '" + sName + "%' ";
		}
		if (nOP != 0) {
			sql += " AND nOP=" + nOP + " ";
		}
		if (nprovinceId!=0&&nprovinceId!=419) {
			sql += " AND nProvinceId = " + nprovinceId + " ";
		}
		if (ncityId != 0) {
			sql += " AND nCityId=" + ncityId + " ";
		}
		String sql_count = "SELECT count(1) FROM tbmobilerelarea with (nolock) WHERE 1=1" + sql;
		String sql_page = "SELECT  nMobileRelAreaId,sMoblieNo,nProvinceId,sProvinceName,nCityId,sCityName,nOP  FROM tbmobilerelarea with (nolock) WHERE 1=1 " + sql
				+ order;

		return pageQuery(sql_page,sql_count,pageRequest,new BeanPropertyRowMapper(TbMobileRelArea.class));
	}

	public Page searchAreaCenterByPageReq(PageRequest<Map> pageRequest) {		
		Map map = pageRequest.getFilters();
		String sql = "";
		String order = "  order by nCenterNoRelAreaId desc";
		String sName = "";
		int nprovinceId = 0;
		int nOP = 0;
		int ncityId=0;
		
		if(map.get("sName") != null)
			sName = (String) map.get("sName");
		if(map.get("nprovinceId") != null&&!map.get("nprovinceId").equals(""))
			nprovinceId = Integer.parseInt(map.get("nprovinceId").toString());
		if(map.get("ncityId") != null)
			ncityId = Integer.parseInt(map.get("ncityId").equals("")?"0":map.get("ncityId").toString());
		if(map.get("nop") != null)
			nOP = Integer.parseInt(map.get("nop").toString());
		
		if (!sName.isEmpty()) {
			sql += " AND sCenterNo LIKE '" + sName + "%' ";
		}
		if (nOP != 0) {
			sql += " AND nOP=" + nOP + " ";
		}
		if (nprovinceId!=0&&nprovinceId!=419) {
			sql += " AND nProvinceId = " + nprovinceId + " ";
		}
		if (ncityId != 0) {
			sql += " AND nCityId=" + ncityId + " ";
		}
		String sql_count = "SELECT count(1) FROM tbcenternorelarea with (nolock) WHERE 1=1" + sql;
		String sql_page = "SELECT  nCenterNoRelAreaId,sCenterNo,nProvinceId,sProvinceName,nCityId,sCityName,nOP  FROM tbcenternorelarea with (nolock) WHERE 1=1 " + sql
				+ order;

		return pageQuery(sql_page,sql_count,pageRequest,new BeanPropertyRowMapper(TbCenterNorelArea.class));
	}
	
	public Page searchareaManager(PageRequest<Map> pageRequest) {
		Map map = pageRequest.getFilters();
		String sql = "";
		String order = "  order by nAreaId desc";
		String sName = "";
		int nprovinceId = 0;
		
		if(map.get("sName") != null)
			sName = (String) map.get("sName");
		if(map.get("nprovinceId") != null&&!map.get("nprovinceId").equals(""))
			nprovinceId = Integer.parseInt(map.get("nprovinceId").toString());
		
		if (!sName.isEmpty()) {
			sql += " AND sName LIKE '%" + sName + "%' ";
		}
		if (nprovinceId!=0&&nprovinceId!=419) {
			sql += " AND nPId = " + nprovinceId + " ";
		}
		String sql_count = "SELECT count(1) FROM tbarea with (nolock) WHERE 1=1" + sql;
		String sql_page = "SELECT nAreaId,nPId,sName,sCode FROM tbarea with (nolock) WHERE 1=1 " + sql
				+ order;

		return pageQuery(sql_page,sql_count,pageRequest,new BeanPropertyRowMapper(TbArea.class));
	}
	
	public boolean updateareaManager(TbArea entity) {
		boolean result=false;
		String sql = "update tbarea SET "
			+ " nPId = :nPId,sName = :sName,sCode=:sCode  "
			+ " where nAreaId=:nAreaId";
		
		try {
			int i=getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(entity));
			if(i>0){
				result=true;
			}
		} catch (Exception e) {
			result=false;
			e.printStackTrace();
		}
		
		return result;
	}
	
	public boolean addareaManager(TbArea entity) {
		boolean result=false;
		
		String sql = "insert into tbarea " 
			 + " (nPId,sName,sCode,nType) " 
			 + " values "
			 + " (:nPId,:sName,:sCode,129) ";
		try {
			int i=getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(entity));
			if(i>0){
				result=true;
			}
		} catch (Exception e) {
			result=false;
			e.printStackTrace();
		}
		return result;
	}
	
	public List<Map<String, Object>> selectsProvince(){
		String sql="SELECT nAreaId,sName,sCode FROM tbarea WHERE nPId=0";
		List<Map<String, Object>> provincelist=getSimpleJdbcTemplate().queryForList(sql);
		return provincelist;
	}

	public List<Map<String, Object>> selectsCitybyProvince(int nProvinceId){
		String sql="SELECT nAreaId,sName,sCode FROM tbarea WHERE nPId=?";
		List<Map<String, Object>> citylist=getSimpleJdbcTemplate().queryForList(sql,nProvinceId);
		return citylist;
	}
}
